package org.shan.db.orm;

import com.mlnx.common.utils.MyLog;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SqliteDaoSupport<T extends Serializable, PK extends Serializable> {

    private MyLog log = MyLog.getLog(getClass());

    private String tableName;
    private EntityOperation<T> eo;

    private String dbUrl = "jdbc:sqlite:test.db";

    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public SqliteDaoSupport(Class<T> entityClass) {
        this(entityClass, "jdbc:sqlite:test.db");
    }

    public SqliteDaoSupport(Class<T> entityClass, String dbUrl) {
        eo = new EntityOperation<T>(entityClass);
        this.tableName = eo.tableName;
        this.dbUrl = dbUrl;
    }

    public Connection getConnection() throws SQLException {
        Connection c = DriverManager.getConnection(dbUrl);
        return c;
    }

    public int createTable(String sql) throws SQLException {

        Connection connection = null;
        try {
            connection = getConnection();
            Statement statement = connection.createStatement();

            int count = statement.executeUpdate(sql);
            statement.close();

            return count;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    public T get(PK pk) throws Exception {
        QueryRule queryRule = QueryRule.getInstance();
        queryRule.andEqual(this.eo.pkColum, pk);
        return selectUnique(queryRule);
    }

    /**
     * 根据查询条件获取一条唯一的记录
     *
     * @param queryRule
     * @return
     */
    public T selectUnique(QueryRule queryRule) throws Exception {
        List<T> r = this.select(queryRule);
        return r == null ? null : r.get(0);
    }

    /**
     * 根据查询条件获得一个对象
     *
     * @param queryRule
     * @return
     */
    public List<T> select(QueryRule queryRule) throws SQLException {
        QueryRuleSqlBulider bulider = new QueryRuleSqlBulider(queryRule);
        String whereSql = bulider.getWhereSql();
        String orderSql = bulider.getOrderSql();
        StringBuffer sql = new StringBuffer("select " + this.eo.allColumn + " from " + this.getTableName());
        if (!(whereSql == null || whereSql.trim().length() == 0)) {
            sql.append(" where " + whereSql);
        }
        if (!(orderSql == null || orderSql.trim().length() == 0)) {
            sql.append(" order by " + orderSql);
        }

        log.debug("查询SQL:{}", sql.toString());

        return select(sql.toString(), bulider.getValues());
    }

    public T selectOne(QueryRule queryRule) throws SQLException {
        QueryRuleSqlBulider bulider = new QueryRuleSqlBulider(queryRule);
        String whereSql = bulider.getWhereSql();
        String orderSql = bulider.getOrderSql();
        StringBuffer sql = new StringBuffer("select " + this.eo.allColumn + " from " + this.getTableName());
        if (!(whereSql == null || whereSql.trim().length() == 0)) {
            sql.append(" where " + whereSql);
        }
        if (!(orderSql == null || orderSql.trim().length() == 0)) {
            sql.append(" order by " + orderSql);
        }

        sql.append(" limit 1");

        log.debug("查询SQL:{}", sql.toString());

        List<T> results = select(sql.toString(), bulider.getValues());
        if (results.size() > 0) {
            return results.get(0);
        } else {
            return null;
        }
    }

    /**
     * 获取满足条件的记录数
     *
     * @param queryRule
     * @return
     * @throws Exception
     */
    public long getCount(QueryRule queryRule) throws Exception {
        QueryRuleSqlBulider bulider = new QueryRuleSqlBulider(queryRule);
        String whereSql = bulider.getWhereSql();
        StringBuffer sql = new StringBuffer("select count(1) from " + this.getTableName());
        if (!(whereSql == null || whereSql.trim().length() == 0)) {
            sql.append(" where " + whereSql);
        }
        return selectCount(sql.toString());
    }

    /**
     * 插入一条新的记录
     *
     * @param entity
     * @return
     */
    public int insert(T entity) throws Exception {
        SqlBulider bulider = new SqlBulider();
        Map<String, Object> params = this.eo.parse(entity);
        String sql = bulider.buliderForInsert(this.getTableName(), params);

        log.debug("插入SQL:{}", sql);

        return insert(sql);
    }

    public int insertBatch(List<T> entitys) throws Exception {

        int result = 0;
        for (T entity : entitys) {
            result += insert(entity);
        }

        return result;
    }

    public int deleteById(Integer id) throws SQLException {
        return delete(QueryRule.getInstance().andEqual("id", id));
    }

    public int delete(QueryRule queryRule) throws SQLException {
        QueryRuleSqlBulider bulider = new QueryRuleSqlBulider(queryRule);
        String whereSql = bulider.getWhereSql();
        StringBuffer sql = new StringBuffer("delete from " + this.getTableName());
        if (!(whereSql == null || whereSql.trim().length() == 0)) {
            sql.append(" where " + whereSql);
        }

        log.debug("删除SQL:{}", sql.toString());

        return delete(sql.toString(), bulider.getValues());
    }

    private void appendParameter(PreparedStatement statement, Object[] objects) throws SQLException {

        if (objects != null) {
            for (int i = 0; i < objects.length; i++) {
                Object object = objects[i];

                int paramIndex = i + 1;
                if (object instanceof Integer) {
                    statement.setInt(paramIndex, (Integer) object);
                } else if (object instanceof String) {
                    statement.setString(paramIndex, object.toString());
                }
            }
        }
    }

    private List<T> select(String sql, Object[] values) throws SQLException {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            appendParameter(statement, values);
            ResultSet rs = statement.executeQuery();

            List<T> results = new ArrayList<>();
            while (rs.next()) {
                T t = eo.rowMapper.mapRow(rs, 0);
                results.add(t);
            }
            statement.close();
            rs.close();

            return results;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    private int selectCount(String sql) throws SQLException {

        Connection connection = null;
        try {
            connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);

            rs.next();
            int row = rs.getInt(1);

            statement.close();
            rs.close();

            return row;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    private int insert(String sql) throws SQLException {

        Connection connection = null;
        try {
            connection = getConnection();
            Statement statement = connection.createStatement();

            int count = statement.executeUpdate(sql);
            statement.close();

            return count;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    private int delete(String sql, Object[] values) throws SQLException {

        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            appendParameter(statement, values);

            int count = statement.executeUpdate();
            statement.close();

            return count;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    /**
     * 获取tableName
     *
     * @return
     */
    protected String getTableName() {
        return null == this.tableName ? this.eo.tableName : this.tableName;
    }
}
